package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import modelo.Aluno;
import modelo.Aula;
import modelo.Frequencia;
import conexao.ConnectionManager;

public class FrequenciaDaoImp implements FrequenciaDAO{
	
	private final String INSERT = "insert into frequencia (horaEntrada, horaSaida, dataP, idAula, idAluno) values (?,?,?,?,?)";
	private final String UPDATE = "update frequencia set horaEntrada = ?, horaSaida = ?, dataP = ?, idAula = ?, idAluno = ? where idFrequencia = ?";
	private final String DELETE = "delete from frequencia where idFrequencia = ?";
	private final String ALL = "select * from frequencia";
	private final String NOME = "select * from aluno where nome like ?"; 
	private final String BUSCARPORALUNO = "SELECT f.idFrequencia, f.horaEntrada, f.horaSaida, f.dataP, f.idAula, f.idAluno, t.nome " +
			"FROM Frequencia AS f JOIN aluno AS a ON f.idAluno = a.idAluno " +
			"join Turma as t on a.idTurma = t.idTurma WHERE a.idAluno = ?";
	private final String BUSCARPORNOME = "SELECT f.horaEntrada, f.horaSaida, f.dataP, f.idAula, f.idAluno, t.nome " +
			"FROM Frequencia AS f JOIN aluno AS a ON f.idAluno = a.idAluno " +
			"join Turma as t on a.idTurma = t.idTurma WHERE a.nome = ?";
	private final String CHECARENTRADA = "select * from frequencia where horaEntrada = ?";
	private final String CHECARSAIDA = "select * from frequencia where horaSaida = ?";
	private final String MEDIA =  "SELECT COUNT(idFrequencia) FROM frequencia where idAluno = ?";
	
	

	
private static Connection connection;
	
	public FrequenciaDaoImp() {
		openConnection();
	}
	
	public Frequencia checarSaida(String horaSaida){
		List<Frequencia> frequencias = new ArrayList<Frequencia>();
		
		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = ConnectionManager.getInstance().getConnection().prepareStatement(CHECARSAIDA);
			ps.setObject(1, horaSaida);
			
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				Frequencia frequencia = new Frequencia();
				frequencia.setIdFrequencia(rs.getInt("idFrequencia"));
				frequencia.setHoraEntrada(rs.getString("horaEntrada"));
				frequencia.setHoraSaida(rs.getString("horaSaida"));
				frequencia.setData(rs.getString("dataP"));
				Aula aula = DAOFactory.getAulaDAO().buscarPorID(rs.getInt("idAula"));
				frequencia.setAula(aula);
				Aluno aluno = DAOFactory.getAlunoDAO().buscarPorID(rs.getInt("idAluno"));
				frequencia.setAluno(aluno);
				frequencias.add(frequencia);
			}
		} catch (SQLException e) {
			Logger.getLogger(FrequenciaDaoImp.class.getName()).log(Level.SEVERE,null, e);
		}finally{
			this.closeConnection();
		}
		if(frequencias.isEmpty()){
			return null;
		}else{
			return frequencias.get(0);
		}
	}
	public Frequencia checarEntrada(String horaEntrada){
		List<Frequencia> frequencias = new ArrayList<Frequencia>();
		
		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = ConnectionManager.getInstance().getConnection().prepareStatement(CHECARENTRADA);
			ps.setObject(1, horaEntrada);
			
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				Frequencia frequencia = new Frequencia();
				frequencia.setIdFrequencia(rs.getInt("idFrequencia"));
				frequencia.setHoraEntrada(rs.getString("horaEntrada"));
				frequencia.setHoraSaida(rs.getString("horaSaida"));
				frequencia.setData(rs.getString("dataP"));
				Aula aula = DAOFactory.getAulaDAO().buscarPorID(rs.getInt("idAula"));
				frequencia.setAula(aula);
				Aluno aluno = DAOFactory.getAlunoDAO().buscarPorID(rs.getInt("idAluno"));
				frequencia.setAluno(aluno);
				frequencias.add(frequencia);
			}
		} catch (SQLException e) {
			Logger.getLogger(FrequenciaDaoImp.class.getName()).log(Level.SEVERE,null, e);
		}finally{
			this.closeConnection();
		}
		if(frequencias.isEmpty()){
			return null;
		}else{
			return frequencias.get(0);
		}
	}
	public Frequencia salvar(Frequencia e) {
		try{
		PreparedStatement ps = null;
		openConnection();
		ps = ConnectionManager.getInstance().getConnection().prepareStatement(INSERT);
		
		ps.setObject(1, e.getHoraEntrada());
		ps.setObject(2, e.getHoraSaida());	
		ps.setObject(3, e.getData());
		/*
		ps.setTimestamp(1, new Timestamp(e.getHoraEntrada().getTime()));
		ps.setTimestamp(2, new Timestamp(e.getHoraSaida().getTime()));
		ps.setTimestamp(3, new Timestamp(e.getData().getTime()));
		*/
		ps.setObject(4, e.getAula().getIdAula());
		ps.setObject(5, e.getAluno().getIdAluno());
		
		
		ps.executeUpdate();
		
	} catch (SQLException ex) {
		Logger.getLogger(FrequenciaDaoImp.class.getName()).log(Level.SEVERE, null, ex);
	} finally {
		this.closeConnection();
	}
	return e;
	}
	
	public void alterar(Frequencia e) {
		PreparedStatement ps = null;
		openConnection();
		try {
			ps =ConnectionManager.getInstance().getConnection().prepareStatement(UPDATE);
			//update frequencia set horaEntrada = ?, horaSaida = ?, dataP = ?, idAula = ?, idAluno = ? where idFrequencia = ?";
			ps.setObject(1, e.getHoraEntrada());
			ps.setObject(2, e.getHoraSaida());
			ps.setObject(3, e.getData());
			
			//ps.setTimestamp(1, new Timestamp(e.getHoraEntrada().getTime()));
			//ps.setTimestamp(2, new Timestamp(e.getHoraSaida().getTime()));
			//ps.setTimestamp(3, new Timestamp(e.getData().getTime()));
			
			ps.setInt(4, e.getAula().getIdAula());
			ps.setInt(5, e.getAluno().getIdAluno());
			ps.setInt(6, e.getIdFrequencia());
			
			ps.executeUpdate();

		} catch (SQLException ex) {
			Logger.getLogger(FrequenciaDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		
	}
	
	public void deletar(Frequencia e) {
		try {
			PreparedStatement ps = null;
			ps = ConnectionManager.getInstance().getConnection().prepareStatement(DELETE);

			ps.setObject(1, e.getIdFrequencia());
			
			ps.executeUpdate();

		} catch (SQLException ex) {
			Logger.getLogger(FrequenciaDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
	}
	
	public List<Frequencia> buscarPorNome(String nome) {
        List<Frequencia> frequencias = new ArrayList<Frequencia>();
        openConnection();
        try {
                PreparedStatement ps = null;
                ResultSet rs = null;
                
                ps = ConnectionManager.getInstance().getConnection().prepareStatement(BUSCARPORNOME);
                ps.setObject(1, nome);
                
                rs = ps.executeQuery();
                
                while (rs.next()) {
                        Frequencia frequencia = new Frequencia();
                        //frequencia.setIdFrequencia(rs.getInt("idFrequencia"));
                        frequencia.setHoraEntrada(rs.getString("horaEntrada"));
                        frequencia.setHoraSaida(rs.getString("horaSaida"));
                        frequencia.setData(rs.getString("dataP"));
                        Aluno alunoo = DAOFactory.getAlunoDAO().buscarPorID(rs.getInt("idAluno"));
                        frequencia.setAluno(alunoo);
                        Aula aula = DAOFactory.getAulaDAO().buscarPorID(rs.getInt("idAula"));
                        frequencia.setAula(aula);
                        //frequencia.setPorcentagem(rs.getDouble("porcentagem"));
                        frequencias.add(frequencia);
                        
                }

        } catch (SQLException ex) {
                Logger.getLogger(AlunoDaoImp.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
                this.closeConnection();
        }if (frequencias.isEmpty()) {
                return null;
        } else {
                return frequencias;
        }
}

	
	public Frequencia buscarPorAluno(Aluno aluno) {
		Frequencia frequencia = null;

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = ConnectionManager.getInstance().getConnection().prepareStatement(BUSCARPORALUNO);
			ps.setInt(1, aluno.getIdAluno());
			
			System.out.println(BUSCARPORALUNO);
			System.out.println(aluno.getIdAluno());
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				frequencia = new Frequencia();
				frequencia.setIdFrequencia(rs.getInt("idFrequencia"));
				frequencia.setHoraEntrada(rs.getString("horaEntrada"));
				frequencia.setHoraSaida(rs.getString("horaSaida"));
				frequencia.setData(rs.getString("dataP"));
				Aluno alunoo = DAOFactory.getAlunoDAO().buscarPorID(rs.getInt("idAluno"));
				frequencia.setAluno(alunoo);
				Aula aula = DAOFactory.getAulaDAO().buscarPorID(rs.getInt("idAula"));
				frequencia.setAula(aula);
				//DateFormat formatter = new SimpleDateFormat("HH:mm:ss");  
				
				//Date dataHora = (Date)formatter.parse(rs.getDate("horaEntrada"));  
				//Pegar a data do banco e transformar para a data do Java
				
				//frequencia.setHoraEntrada(dataHora);
				frequencia.setData(rs.getString("dataP"));
				
			}

		} catch (SQLException ex) {
			Logger.getLogger(AlunoDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		return frequencia;
		
	}
	
	public List<Frequencia> findAll() {
		List<Frequencia> frequencias = new ArrayList<Frequencia>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(ALL);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				
				Frequencia frequencia = new Frequencia();
				frequencia.setIdFrequencia(rs.getInt("idFrequencia"));
				frequencia.setHoraEntrada(rs.getString("horaEntrada"));
				frequencia.setHoraSaida(rs.getString("horaSaida"));
				frequencia.setData(rs.getString("dataP"));
				Aula aula = DAOFactory.getAulaDAO().buscarPorID(rs.getInt("idAula"));
				frequencia.setAula(aula);
				
				frequencias.add(frequencia);
			}

		} catch (SQLException ex) {
			Logger.getLogger(FrequenciaDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		return frequencias;
	}

	
	public boolean isConnectionClose() {
		return false;
		}

	
	public void openConnection() {
		if ( connection == null) {
			connection = ConnectionManager.getInstance().getConnection();
		}
		
		if ( isConnectionClose() ) {
			connection = ConnectionManager.getInstance().getConnection();
		}	
	}

	
	public void closeConnection() {
		if ( connection != null && !this.isConnectionClose()) {
			ConnectionManager.getInstance().closeConnection(connection);
		}
	}

	

	@Override
	public Frequencia CalculoMediaDePresenca(Aula totalDeAulas,	double porcentagem, int idFrequencia) {
		porcentagem = 100;
		
		return null;
		
	}

	

	
	

}


